// Helper to build WHERE clauses and params for filtering
function buildFilterClause(filters, paramStartIndex = 1) {
    let clause = '';
    const params = [];
    let currentParamIndex = paramStartIndex;

    const conditions = [];

    if (filters.title) {
        conditions.push(`m.title ILIKE $${currentParamIndex++}`); // Using m.title from main message table
        params.push(`%${filters.title}%`);
    }
    if (filters.target_type) {
        conditions.push(`m.target_type = $${currentParamIndex++}`);
        params.push(filters.target_type);
    }
    if (filters.priority) {
        conditions.push(`m.priority = $${currentParamIndex++}`);
        params.push(filters.priority);
    }

    // Status filtering logic:
    // 1-草稿, 2-已发布, 3-未生效, 4-生效中, 5-已过期 (these are derived for API)
    // DB status: 1-草稿, 2-已发布, (3-未生效, 4-已过期, 5-已删除 are actual DB states)
    // Your spec uses different status numbers for filtering than DB.
    // status: 1-草稿, 2-已发布, 3-未生效, 4-生效中, 5-已过期 (Filter options)
    // DB states: 1-草稿, 2-已发布 (message active), 3-未生效 (DB: published but future start), 4-已过期 (DB: published but past end), 5-已删除
    const now = Date.now();
    if (filters.status) {
        const statusFilter = parseInt(filters.status);
        if (statusFilter === 1) { // 草稿
            conditions.push(`m.status = 1`);
        } else if (statusFilter === 2) { // 已发布 (could mean published and not necessarily active now)
            conditions.push(`m.status = 2`);
        } else if (statusFilter === 3) { // 未生效 (Published, but effective_start_at > now)
            conditions.push(`m.status = 2 AND m.effective_start_at > $${currentParamIndex++}`);
            params.push(now);
        } else if (statusFilter === 4) { // 生效中 (Published, and now is within effective range)
            conditions.push(`m.status = 2 AND m.effective_start_at <= $${currentParamIndex} AND m.effective_end_at >= $${currentParamIndex + 1}`);
            params.push(now);
            params.push(now);
            currentParamIndex += 2;
        } else if (statusFilter === 5) { // 已过期 (Published, but effective_end_at < now)
            conditions.push(`m.status = 2 AND m.effective_end_at < $${currentParamIndex++}`);
            params.push(now);
        }
        // Note: DB status 5 (已删除) is usually excluded unless explicitly queried.
    }


    if (filters.start_time) { // Assuming these are message's effective_start_at or published_at
        conditions.push(`m.effective_start_at >= $${currentParamIndex++}`);
        params.push(filters.start_time);
    }
    if (filters.end_time) {
        conditions.push(`m.effective_end_at <= $${currentParamIndex++}`);
        params.push(filters.end_time);
    }

    if (conditions.length > 0) {
        clause = 'WHERE ' + conditions.join(' AND ');
    }

    return { clause, params, nextParamIndex: currentParamIndex };
}


module.exports = { buildFilterClause };